package com.googlecode.garbagecan.dbtest.vertica;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

public class TestTransaction {

	private static final String driver = "com.vertica.jdbc.Driver";
	private static final String url = "jdbc:vertica://padev3:5433/padb";
	private static final String username = "pauser";
	private static final String password = "papassword";
	
//	private static final String driver = "com.vertica.Driver";
//	private static final String url = "jdbc:vertica://perf4:5552/fkong2";
//	private static final String username = "pa80";
//	private static final String password = "fkong2";
	
	public static void main(String[] args) throws Exception {
		TestTransaction test = new TestTransaction();
		//test.getConnection();
		//test.test1();
		//test.test2();
		test.test3();
	}
	
	private void test1() throws Exception {
		Connection conn = getConnection();
		
		PreparedStatement ps1 = conn.prepareStatement("insert into t1(id, col1, col2, col3)values(?,?,?,?)");
		for (int i = 1; i <= 5; i++) {
			ps1.setInt(1, i);
			ps1.setObject(2, "value1");
			ps1.setObject(3, "value2");
			ps1.setObject(4, "value3");
			ps1.addBatch();
		}
		ps1.executeBatch();
		
		PreparedStatement ps2 = conn.prepareStatement("select * from t1");
		ResultSet rs = ps2.executeQuery();
		System.out.println(rs.next());
		
		Thread.sleep(60 * 1000);
		
		conn.commit();
		conn.close();
	}
	
	private void test2() throws Exception {
		Connection conn = getConnection();
		
		PreparedStatement ps1 = conn.prepareStatement("insert into t1(id, col1, col2, col3)values(?,?,?,?)");
		for (int i = 1; i <= 1; i++) {
			ps1.setInt(1, i);
			ps1.setObject(2, "value1");
			ps1.setObject(3, "value2");
			ps1.setObject(4, "value3");
			ps1.addBatch();
		}
		ps1.executeBatch();

		PreparedStatement ps2 = conn.prepareStatement("insert into t2(id, col1, col2, col3)values(?,?,?,?)");
		for (int i = 1; i <= 1; i++) {
			ps2.setInt(1, i);
			ps2.setObject(2, "value1");
			ps2.setObject(3, "value2");
			ps2.setObject(4, "value3");
			//ps2.setObject(4, "value3=========================================================");
			ps2.addBatch();
		}
		ps2.executeBatch();
		
		//Thread.sleep(60 * 1000);
		
		conn.commit();
		conn.close();
	}
	
	private void test3() throws Exception {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("insert into t1(id, col1, col2, col3)values(?,?,?,?)");
			ParameterMetaData pmd = ps.getParameterMetaData();
			
			String value3 = "=========================================================";
			if (pmd.getParameterTypeName(4).equalsIgnoreCase("VARCHAR")) {
				int maxLength = pmd.getPrecision(4);
				if (value3.toString().length() > maxLength) {
					value3 = value3.substring(0, maxLength);
				}
			}
			
//			ps.setInt(1, 1);
			ps.setNull(1, Types.VARCHAR);
			ps.setObject(2, "value1");
			ps.setObject(3, "value2");
			ps.setObject(4, value3);
			ps.addBatch();
			
			//ps.setInt(1, 2);
			ps.setNull(1, Types.VARCHAR);
			ps.setObject(2, "value11");
			ps.setObject(3, "value22");
			ps.setObject(4, value3);
			ps.addBatch();
			
			int[] results = ps.executeBatch();
			System.out.println(Arrays.toString(results));
		} catch(SQLException ex) {
			if (ex instanceof BatchUpdateException) {
				System.out.println(Arrays.toString(((BatchUpdateException) ex).getUpdateCounts()));
			}
			ex.printStackTrace();
		}
		ps.clearBatch();
		
		conn.commit();
		conn.close();
	}
	
	private Connection getConnection() throws Exception {
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url, username, password);
		conn.setAutoCommit(false);
		return conn;
	}
	
	private String query(String sql) throws Exception {
		Connection conn = getConnection();
		QueryRunner queryRunner = new QueryRunner();
		String value = queryRunner.query(conn, sql, new ResultSetHandler<String>() {
			public String handle(ResultSet rs) throws SQLException {
				if (!rs.next()) {
					return null;
				}
				String value = rs.getString(1);
				return value;
			}
		});
		conn.close();
		return value;
	}
}
